use calamine::{Data, DataType, Range};
use serde::{de, Deserialize};
use shq_common::prelude::{alpha_to_index, Parameter};

#[derive(Debug, Deserialize)]
pub struct Config {
    pub title: String,
    pub db_table: Option<DbTable>,
    #[serde(default)]
    pub params: Vec<Parameter>,
    pub sheets: Sheets,
    pub import_after_sql: Option<SqlList>,
}
impl Config {
    pub fn parse(&self) -> anyhow::Result<()> {
        if self.title.is_empty() {
            anyhow::bail!("标题不能为空");
        }
        if let Some(db_table) = &self.db_table {
            if db_table.table_name.is_empty() {
                anyhow::bail!("表名不能为空");
            }
        } else {
            for s in self.sheets.as_vec() {
                if s.tables.as_vec().is_empty() {
                    anyhow::bail!("表不能为空");
                }
                for t in s.tables.as_vec() {
                    if t.db_table.is_none() {
                        return Err(anyhow::anyhow!(
                            "没有配置顶层db_table,table的db_table必须配置"
                        ));
                    }
                }
            }
        }
        if self.sheets.as_vec().is_empty() {
            anyhow::bail!("sheets不能为空");
        }
        self.sheets.parse()?;
        Ok(())
    }
}
#[derive(Debug, Deserialize)]
pub struct DbTable {
    pub table_name: String,
    pub save_mode: SaveMode,
    pub unique_keys: Vec<String>,
}
#[derive(Debug, PartialEq, Eq, Deserialize)]
pub enum SaveMode {
    Overwrite,
    IgnoreIfExists,
    ErrorIfExists,
}
#[derive(Debug)]
pub enum Sheets {
    Single(Sheet),
    Multi(Vec<Sheet>),
}
impl <'de> de::Deserialize<'de> for Sheets {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct SheetsVisitor;
        impl<'de> de::Visitor<'de> for SheetsVisitor {
            type Value = Sheets;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("需要一个单独的Sheet或Sheet数组")
            }
            fn visit_seq<A>(self, mut seq: A) -> Result<Self::Value, A::Error>
            where
                A: de::SeqAccess<'de>,
            {
                let mut sheets = Vec::new();
                while let Some(sheet) = seq.next_element()? {
                    sheets.push(sheet);
                }
                if sheets.len() == 1 {
                    Ok(Sheets::Single(sheets.remove(0)))
                } else {
                    Ok(Sheets::Multi(sheets))
                }
            }
            fn visit_map<M>(self, map: M) -> Result<Self::Value, M::Error>
            where
                M: de::MapAccess<'de>,
            {
                let sheet = Sheet::deserialize(de::value::MapAccessDeserializer::new(map))?;
                Ok(Sheets::Single(sheet))
            }
        }
        deserializer.deserialize_any(SheetsVisitor)
    }
}
impl Sheets {
    pub fn as_vec(&self) -> Vec<&Sheet> {
        match self {
            Sheets::Single(sheet) => vec![sheet],
            Sheets::Multi(sheets) => sheets.iter().collect(),
        }
    }
    pub fn parse(&self) -> anyhow::Result<()> {
        match self {
            Sheets::Single(sheet) => {
                sheet.parse()?;
            }
            Sheets::Multi(sheets) => {
                for sheet in sheets {
                    sheet.parse()?;
                }
            }
        }
        Ok(())
    }
}
#[derive(Debug, Deserialize)]
pub struct Sheet {
    pub sheet_name: SheetName,
    pub tables: Tables,
}
impl Sheet {
    pub fn parse(&self) -> anyhow::Result<()> {
        if self.tables.as_vec().is_empty() {
            anyhow::bail!("表不能为空");
        }
        self.tables.parse()?;
        Ok(())
    }
}
#[derive(Debug)]
pub enum SheetName {
    Name(String),
    Index(u16),
    Multi(Vec<u16>),
    Range((u16, u16)),
    All,
}
impl<'de> de::Deserialize<'de> for SheetName {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct SheetNameVisitor;
        impl<'de> de::Visitor<'de> for SheetNameVisitor {
            type Value = SheetName;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("a string or an array of strings")
            }
            fn visit_str<E>(self, value: &str) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value == "*" {
                    Ok(SheetName::All)
                } else if value.chars().all(|c| c.is_ascii_digit()) {
                    let idx: u16 = value.parse().unwrap();
                    if idx == 0 {
                        return Err(de::Error::custom("index必须从1开始"));
                    }
                    Ok(SheetName::Index(value.parse().unwrap()))
                } else if value.contains(':') {
                    let mut parts = value.split(':');
                    let start = parts.next().unwrap().parse().unwrap();
                    let end = parts.next().unwrap().parse().unwrap();
                    if start > end {
                        return Err(de::Error::custom("开始序号必须小于等于截止序号"));
                    }
                    if start == 0 || end == 0 {
                        return Err(de::Error::custom("开始序号和截止序号必须从1开始"));
                    }
                    Ok(SheetName::Range((start, end)))
                } else {
                    Ok(SheetName::Name(value.to_string()))
                }
            }
            fn visit_seq<A>(self, mut seq: A) -> Result<Self::Value, A::Error>
            where
                A: de::SeqAccess<'de>,
            {
                let mut vec = Vec::new();
                while let Some(value) = seq.next_element()? {
                    vec.push(value);
                }
                Ok(SheetName::Multi(vec))
            }
        }
        deserializer.deserialize_any(SheetNameVisitor)
    }
}

#[derive(Debug)]
pub enum Tables {
    Single(Table),
    Multi(Vec<Table>),
}
impl <'de> de::Deserialize<'de> for Tables {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct TablesVisitor;
        impl<'de> de::Visitor<'de> for TablesVisitor {
            type Value = Tables;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("需要一个单独的Table或Table数组")
            }
            fn visit_seq<A>(self, mut seq: A) -> Result<Self::Value, A::Error>
            where
                A: de::SeqAccess<'de>,
            {
                let mut tables = Vec::new();
                while let Some(table) = seq.next_element()? {
                    tables.push(table);
                }
                if tables.len() == 1 {
                    Ok(Tables::Single(tables.remove(0)))
                } else {
                    Ok(Tables::Multi(tables))
                }
            }
            fn visit_map<M>(self, map: M) -> Result<Self::Value, M::Error>
            where
                M: de::MapAccess<'de>,
            {
                let table = Table::deserialize(de::value::MapAccessDeserializer::new(map))?;
                Ok(Tables::Single(table))
            }
        }
        deserializer.deserialize_any(TablesVisitor)
    }
}
impl Tables {
    pub fn as_vec(&self) -> Vec<&Table> {
        match self {
            Tables::Single(table) => vec![table],
            Tables::Multi(tables) => tables.iter().collect(),
        }
    }
    pub fn parse(&self) -> anyhow::Result<()> {
        match self {
            Tables::Single(table) => {
                table.parse()?;
            }
            Tables::Multi(tables) => {
                for table in tables {
                    table.parse()?;
                }
            }
        }
        Ok(())
    }
}

#[derive(Debug, Deserialize)]
pub struct Table {
    pub db_table: Option<DbTable>,
    pub category: Option<Category>,
    pub row_range: Option<RowRange>,
    pub columns: Vec<Column>,
}
impl Table {
    pub fn parse(&self) -> anyhow::Result<()> {
        if self.columns.is_empty() {
            anyhow::bail!("表必须配置列");
        }
        if let Some(db_table) = &self.db_table {
            if db_table.table_name.is_empty() {
                anyhow::bail!("表名不能为空");
            }
        }
        for c in self.columns.iter() {
            if c.col_name.is_empty() {
                anyhow::bail!("列名不能为空");
            }
        }
        if let Some(category) = &self.category {
            if category.table_name.is_empty() {
                anyhow::bail!("分类表名不能为空");
            }
            if category.start_col.bytes().any(|b| !b.is_ascii_uppercase()) {
                anyhow::bail!("分类起始列号必须是大写字母");
            }
            if category.start_row == 0 {
                anyhow::bail!("分类起始行号必须从1开始");
            }
            if category.id_col_name.is_empty() {
                anyhow::bail!("分类ID列名不能为空");
            }
            if category.level_col_names.is_empty() {
                anyhow::bail!("分类层级列名不能为空");
            }
            if self.row_range.is_none() {
                anyhow::bail!("分类表必须配置行范围");
            }
            for (i, col) in category.level_col_names.iter().enumerate() {
                if col.is_empty() {
                    anyhow::bail!("分类表第{}层级列名不能为空", i + 1);
                }
            }
            if self.columns.iter().any(|c| {
                if let CellValue::Seek(_) = c.cell_value {
                    true
                } else {
                    false
                }
            }) {
                anyhow::bail!("分类表columns中必须不能配置为?行号");
            }
            if !self
                .columns
                .iter()
                .any(|c| c.cell_value == CellValue::CrossID)
            {
                anyhow::bail!("分类表columns中必须配置配置CrossID");
            }
            if !self
                .columns
                .iter()
                .any(|c| c.cell_value == CellValue::CrossValue)
            {
                anyhow::bail!("分类表columns中必须配置配置CrossValue");
            }
        } else {
            if self
                .columns
                .iter()
                .any(|c| c.cell_value == CellValue::CrossID)
            {
                anyhow::bail!("普通表columns中不能配置CrossID");
            }
            if self
                .columns
                .iter()
                .any(|c| c.cell_value == CellValue::CrossValue)
            {
                anyhow::bail!("普通表columns中不能配置CrossValue");
            }
        }
        Ok(())
    }
}

#[derive(Debug, Deserialize)]
pub struct Category {
    pub table_name: String,
    pub start_col: String,
    pub start_row: u32,
    pub id_col_name: String,
    pub level_col_names: Vec<String>,
}
#[derive(Debug, Deserialize)]
pub struct RowRange {
    pub start: RowNo,
    pub end: RowNo,
    pub skips: Option<Vec<RowNo>>,
}
#[derive(Debug)]
pub enum RowNo {
    Index(u32),
    Cond((String, String)),
}
impl RowNo {
    pub fn to_u32(&self, datas: &Range<Data>, start: u32, offset: i8) -> anyhow::Result<u32> {
        let start = start as usize;
        match self {
            RowNo::Index(idx) => Ok(*idx),
            RowNo::Cond((col, val)) => {
                let row_count = datas.get_size().0;
                let j = alpha_to_index(col) - 1;
                for i in start..=row_count {
                    if let Some(cell) = datas.get_value((i as u32, j)) {
                        let v2 = cell.to_string();
                        if cell.is_empty() || v2.is_empty() {
                            if val == "none" {
                                let idx = (i as i64 + offset as i64) as u32;
                                return Ok(idx + 1);
                            }
                        }
                        if &v2 == val {
                            let idx = (i as i64 + offset as i64) as u32;
                            return Ok(idx + 1);
                        }
                    } else {
                        if val == "none" {
                            let idx = (i as i64 + offset as i64) as u32;
                            return Ok(idx + 1);
                        }
                    }
                }
                for (i, row) in datas.rows().enumerate() {
                    let j = alpha_to_index(col) - 1;
                    if let Some(cell) = row.get(j as usize) {
                        let v2 = cell.to_string();
                        if &v2 == val {
                            return Ok(i as u32);
                        }
                    }
                }
                anyhow::bail!("没有找到符合条件{col}={val}的行号");
            }
        }
    }
}
impl<'de> de::Deserialize<'de> for RowNo {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct RowNoVisitor;
        impl<'de> de::Visitor<'de> for RowNoVisitor {
            type Value = RowNo;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("需要一个字符串或数字")
            }
            fn visit_str<E>(self, value: &str) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value.chars().all(|c| c.is_ascii_digit()) {
                    let idx: u32 = value.parse().unwrap();
                    if idx == 0 {
                        return Err(de::Error::custom("index必须从1开始"));
                    }
                    Ok(RowNo::Index(idx))
                } else {
                    let mut parts = value.split('=');
                    let col = parts.next().unwrap().to_string();
                    if !col.chars().all(|c| c.is_ascii_uppercase()) {
                        return Err(de::Error::custom("列名必须是大写字母"));
                    }
                    let val = parts.next().unwrap().to_string();
                    Ok(RowNo::Cond((col, val)))
                }
            }
            // 新增: 处理整数输入
            fn visit_u64<E>(self, value: u64) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value == 0 {
                    return Err(de::Error::custom("index必须从1开始"));
                }
                // 确保值在u32范围内
                if value > u32::MAX as u64 {
                    return Err(de::Error::custom(format!(
                        "行号 {} 超出了u32的最大值",
                        value
                    )));
                }
                Ok(RowNo::Index(value as u32))
            }
            // 新增: 处理有符号整数输入
            fn visit_i64<E>(self, value: i64) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value <= 0 {
                    return Err(de::Error::custom("index必须是正数且从1开始"));
                }
                // 确保值在u32范围内
                if value > u32::MAX as i64 {
                    return Err(de::Error::custom(format!(
                        "行号 {} 超出了u32的最大值",
                        value
                    )));
                }
                Ok(RowNo::Index(value as u32))
            }
        }
        deserializer.deserialize_any(RowNoVisitor)
    }
}
#[derive(Debug, Deserialize)]
pub struct Column {
    pub col_name: String,
    pub col_desc: String,
    pub cell_value: CellValue,
    pub can_none: Option<bool>,
}
#[derive(Debug, Clone, PartialEq, Eq)]
pub enum CellValue {
    Col(String),
    Cell(String),
    Param(String),
    Seek(u32),
    SheetName,
    RowNo,
    CrossID,
    CrossValue,
}

impl<'de> de::Deserialize<'de> for CellValue {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct CellRangeVisitor;
        impl<'de> de::Visitor<'de> for CellRangeVisitor {
            type Value = CellValue;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("需要一个字符串")
            }
            fn visit_str<E>(self, value: &str) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value.starts_with("?") {
                    let val = value[1..]
                        .parse::<u32>()
                        .or(Err(de::Error::custom("?后是要查找的行号，必须是u32")))?;
                    if val == 0 {
                        return Err(de::Error::custom("?后是要查找的行号，必须大于0"));
                    }
                    Ok(CellValue::Seek(val))
                } else if value.starts_with("@") {
                    Ok(CellValue::Param(value.to_string()))
                } else if value == "SheetName" {
                    Ok(CellValue::SheetName)
                } else if value == "RowNo" {
                    Ok(CellValue::RowNo)
                } else if value == "CrossID" {
                    Ok(CellValue::CrossID)
                } else if value == "CrossValue" {
                    Ok(CellValue::CrossValue)
                } else if value.chars().all(|c| c.is_ascii_uppercase()) {
                    Ok(CellValue::Col(value.to_string()))
                } else {
                    Ok(CellValue::Cell(value.to_string()))
                }
            }
        }
        deserializer.deserialize_any(CellRangeVisitor)
    }
}

#[derive(Debug, Clone, PartialEq, Eq)]
pub enum SqlList {
    Sql(String),
    Sqls(Vec<String>),
    Path(String),
}
impl SqlList {
    pub fn from_file_name(s: &str) -> Self {
        if s.ends_with(".sql") {
            SqlList::Path(s.to_string())
        } else {
            SqlList::Path(format!("{}.sql", s))
        }
    }
}
impl<'de> de::Deserialize<'de> for SqlList {
    fn deserialize<D>(deserializer: D) -> Result<Self, D::Error>
    where
        D: de::Deserializer<'de>,
    {
        struct SqlListVisitor;
        impl<'de> de::Visitor<'de> for SqlListVisitor {
            type Value = SqlList;
            fn expecting(&self, formatter: &mut std::fmt::Formatter) -> std::fmt::Result {
                formatter.write_str("需要一个字符串或字符串数组")
            }
            fn visit_str<E>(self, value: &str) -> Result<Self::Value, E>
            where
                E: de::Error,
            {
                if value.ends_with(".sql") {
                    return Ok(SqlList::Path(value.to_string()));
                } else {
                    Ok(SqlList::Sql(value.to_string()))
                }
            }
            fn visit_seq<A>(self, mut seq: A) -> Result<Self::Value, A::Error>
            where
                A: de::SeqAccess<'de>,
            {
                let mut vec = Vec::new();
                while let Some(value) = seq.next_element()? {
                    vec.push(value);
                }
                Ok(SqlList::Sqls(vec))
            }
        }
        deserializer.deserialize_any(SqlListVisitor)
    }
}
